Create Staging Table in Staging Database and Populate the Staging Tables 10

Download the SQL File attached to the

Run Script that is attached to create your Staging tables in the Staging Database. Once ran, the tables will be made up of these columns.

*Using SSIS Toolbox, Create "LoadStagingTable" Package, the following components will be drag in the Control Flow Dashboard;

Drag Execute SQL Task to Truncate Staging Tables, Double Click and Add Connection manager and Write Sql Query to Truncate Staging Tables
Adding required Variables, Right Click on Control Flow tab - select Variables from the Menu and Add Variables JSONFileName and JSONFilePath and select String Data type and provide path respectively
Drag and drop Sequence Container to Load Source file
Inside Sequence Container, Drag and drop the Script Task to Get JSON File Name , Double Click and Add the Variables and Click on the Edit button and Add the C# code provided

C# Code for Script Task " GetJSONFileName "

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

		public void Main()
		{
            // TODO: Add your code here
            string filepath;
            string filename;

            filepath = Dts.Variables["User::JSONFilePath"].Value.ToString();

            filename = Path.GetFileName(filepath);

            Dts.Variables["User::JSONFileName"].Value = filename;

            // MessageBox.Show(filename);


            Dts.TaskResult = (int)ScriptResults.Success;
		}

Drag and drop the Data Flow Task inside Sequence Container and Double Click

Drag and Drop the Script Component and select Source - Double Click on the Script tab and Add the JSONFilePath variable
Go to the Input and Output Column tab - Add the output columns and enter the column names that matches to the SQL table and make sure that if there is an int column in the sql table schema provide that same int datatype in the output column and for all other datatype select string with 255 length.
Go to the script tab , Click on the Edit Button and Right Click on the Reference and add System.Web.Extensions. Add a class ( Incidents ) to the Main Script and Add the code provided for Class and Main Script.

C# Code for Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace IncidentsNameSpace
{
    class Incidents
    {
        public string IncidentNumber { get; set; }
        public string IncidentType { get; set; }
        public string IncidentDate { get; set; }
        public string IncidentLocation { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string ReportingDistrict { get; set; }
        public string GeoLocation { get; set; }
        public string HandlingUnitID { get; set; }
        public string HandlingUnitName { get; set; }
        public string NumberOfInvolvedDeputies { get; set; }
        public string DeputyRace { get; set; }
        public string NumberOfSuspects { get; set; }
        public string SuspectRace { get; set; }
        public string NumberOfSuspectWounded { get; set; }
        public string NumberOfSuspectDeceased { get; set; }
        public string WeaponInvolvedCategory { get; set; }
        public string WeaponInvolvedCategoryDesc { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }

    }
}

C# Code for Main

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.IO;
using IncidentsNameSpace;

#endregion

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        String jsonFileContent = File.ReadAllText(@"C: \Users\m_kol\Documents\DWH\12.Project1\MyProject\Json File\20201114.JSON");
        JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };
        List<Incidents> IncidentDetails = js.Deserialize<List<Incidents>>(jsonFileContent);
        foreach (Incidents IncidentList in IncidentDetails)
        {
            Output0Buffer.AddRow();
            Output0Buffer.IncidentNumber = IncidentList.IncidentNumber;
            Output0Buffer.IncidentType = IncidentList.IncidentType;
            Output0Buffer.IncidentDate = IncidentList.IncidentDate;
            Output0Buffer.IncidentLocation = IncidentList.IncidentLocation;
            Output0Buffer.City = IncidentList.City;
            Output0Buffer.State = IncidentList.State;
            Output0Buffer.Zip = IncidentList.Zip;
            Output0Buffer.ReportingDistrict = IncidentList.ReportingDistrict;
            Output0Buffer.GeoLocation = IncidentList.GeoLocation;
            Output0Buffer.HandlingUnitID = IncidentList.HandlingUnitID;
            Output0Buffer.HandlingUnitName = IncidentList.HandlingUnitName;
            Output0Buffer.NumberOfInvolvedDeputies = IncidentList.NumberOfInvolvedDeputies;
            Output0Buffer.DeputyRace = IncidentList.DeputyRace;
            Output0Buffer.NumberOfSuspects = IncidentList.NumberOfSuspects;
            Output0Buffer.SuspectRace = IncidentList.SuspectRace;
            Output0Buffer.NumberOfSuspectWounded = IncidentList.NumberOfSuspectWounded;
            Output0Buffer.NumberOfSuspectDeceased = IncidentList.NumberOfSuspectDeceased;
            Output0Buffer.WeaponInvolvedCategory = IncidentList.WeaponInvolvedCategory;
            Output0Buffer.WeaponInvolvedCategoryDesc = IncidentList.WeaponInvolvedCategoryDesc;
            Output0Buffer.Latitude = IncidentList.Latitude;
            Output0Buffer.Longitude = IncidentList.Longitude;

        }

    }
}

Drag and drop the Conditional Split , Double click and add condition to remove the Null values

Drag and Drop the Derived Column transformation and add a new column FileDate and use Expression for DeputyRace, SuspectRace, City, Latitude, Longitude, IncidentLocation that make clean up in these columns and replace them.

Expression Codes For Derived Column Transformation

FileDate  SUBSTRING(@[User::JSONFileName],5,2) + "/" + SUBSTRING(@[User::JSONFileName],7,2) + "/" + SUBSTRING(@[User::JSONFileName],1,4)

DeputyRace	REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DeputyRace,"ASIAN-PACIFIC,ISL","A"),"FILIPINO","F"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")

SuspectRace	REPLACENULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A") == "" ? NULL(DT_WSTR,255) : ((DT_WSTR,255)REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SuspectRace,"ASIAN-PACIFIC,ISL","A"),",,HISPANIC","H"),"BLACK","B"),"WHITE","W"),"HISPANIC","H"),"ASIAN-PACIFIC-ISL","A")),"UNKNOWN")

Drag and drop the Data Conversion transformation - to convert the appropriate Data Types

Drag and drop the OLEDB Destination - add the destination connection manager and select the staging table to load the data

Introduction to c# Scripting in SSIS

Script Task in SSIS

Script Component in SSIS